
#! /bin/bash
HIVE_HOME=/usr/bin/hive


${HIVE_HOME} -S -e "-------------校区报名TopN表：rpt_signup_campus_topn,3.1 校区报名柱状图
INSERT INTO hive.oe_rpt.rpt_signup_campus_topn
SELECT
        year_type,
        month_type,
        day_type,
        date_type,
        line_type,
        group_type_new,
        itcast_school_id,
        itcast_school_name,
        cast(group_type_signup_count AS INT )
FROM oe_dm.dm_signup_message1
WHERE group_type_new IS NOT NULL AND group_type_new = 'line_school' AND date_type = 'day' AND itcast_school_id IS not NULL
;
----上传到MySQL



---------------学科报名TopN表,学科报名柱状图----------------------------
--CREATE TABLE hive.oe_rpt.rpt_signup_subject_topn AS
SELECT DISTINCT
        year_type,
        month_type,
        day_type,
        date_type,
        line_type,
        group_type_new,
        itcast_subject_id,
        itcast_subject_name,
        cast(group_type_signup_count AS INT ) AS group_type_signup_count
FROM oe_dm.dm_signup_message1
WHERE group_type_new IS NOT NULL AND group_type_new = 'line_subject' AND date_type = 'day' AND itcast_subject_id IS not NULL;


---------------------总报名量
CREATE TABLE hive.oe_rpt.rpt_signup_all AS
SELECT
        count(DISTINCT relationship_id) AS count_all
FROM oe_dwb.dwb_signup_message
WHERE  signup_type = '已报名';
----------
CREATE TABLE hive.oe_rpt.rpt_signup_day_topn AS
SELECT   DISTINCT
        year_type,
        month_type,
        day_type,
        date_type,
        line_type,
        group_type_new,
        cast(group_type_signup_count AS INT ) AS group_type_signup_count
FROM oe_dm.dm_signup_message4
WHERE  group_type_new = 'all' AND date_type = 'day' AND group_type_signup_count != 0 AND line_type = 'online' ;

---------------------总报名量中来源渠道为线上访客渠道的报名总量
CREATE TABLE hive.oe_rpt.rpt_signup_online_topn AS
SELECT
        year_type,
        month_type,
        day_type,
        date_type,
        line_type,
        cast(group_type_signup_count AS INT ) AS group_type_signup_count
FROM oe_dm.dm_signup_message1
WHERE group_type_new IS not null and group_type_new = 'line_origin'  AND line_type = 'online' AND group_type_signup_count != 0 AND  date_type = 'day' ;


--------------------3.5 意向用户报名转化率
CREATE TABLE hive.oe_rpt.rpt_signup_relation_ratio1 AS
SELECT
year_type,
        month_type,
        day_type,
        date_type,
        rc_line_type_new,
        rc_signup_count,
        relation_count,
        cast(cast(rc_signup_count AS decimal(38,10))/relation_count*100 AS decimal(5,2)) AS relation_ratio
FROM oe_dm.dm_signup_message4
WHERE rc_line_type_new IS NOT NULL AND relation_count != 0 AND date_type = 'day';



-------------------- 3.6 有效线索报名转化率
CREATE TABLE hive.oe_rpt.rpt_signup_clue_ratio1 AS
SELECT DISTINCT
        year_type,
        month_type,
        day_type,
        date_type,
        cc_line_type_new,
        cc_signup_count,
        clue_count,
        cast(cast(cc_signup_count AS decimal(38,10))/clue_count*100 AS decimal(5,2)) AS clue_ratio
FROM oe_dm.dm_signup_message4
WHERE cc_line_type_new IS NOT NULL AND clue_count != 0 AND date_type = 'day';


----------3.7 日报名趋势图
CREATE TABLE hive.oe_rpt.rpt_signup_day_topn AS
SELECT   DISTINCT
        year_type,
        month_type,
        day_type,
        date_type,
        line_type,
        group_type_new,
        cast(group_type_signup_count AS INT ) AS group_type_signup_count
FROM oe_dm.dm_signup_message4
WHERE group_type_new IS NOT NULL AND group_type_new = 'all' AND date_type = 'day' AND group_type_signup_count != 0;



--------------3.8 校区学科的报名学员TOP
CREATE TABLE hive.oe_rpt.rpt_signup_ss_topn AS
SELECT
        year_type,
        month_type,
        day_type,
        date_type,
        line_type,
        group_type_new,
        itcast_school_id,
        itcast_school_name,
        itcast_subject_id,
        itcast_subject_name,
        cast(group_type_signup_count AS INT) AS signup_count
FROM oe_dm.dm_signup_message3
WHERE  group_type_new = 'line_school_subject' AND date_type = 'day' AND itcast_school_id is not null;



-----------3.9 来源渠道占比
CREATE TABLE hive.oe_rpt.rpt_signup_origin_channel AS
SELECT
        year_type,
        month_type,
        day_type,
        date_type,
        line_type,
        origin_channel,
        cast(group_type_signup_count AS INT) AS signup_count
FROM oe_dm.dm_signup_message3
WHERE  group_type_new = 'line_origin' AND date_type = 'day' AND group_type_signup_count != 0;



----------------------3.10 咨询中心报名贡献
CREATE TABLE hive.oe_rpt.rpt_signup_dept AS
SELECT
        year_type,
        month_type,
        day_type,
        date_type,
        line_type,
        department_id,
        department_name,
        cast(group_type_signup_count AS INT) AS signup_count
FROM oe_dm.dm_signup_message3
WHERE  group_type_new = 'line_dept' AND date_type = 'day' AND group_type_signup_count != 0;"